iT邦幫忙

第 12 屆 iThome 鐵人賽

DAY 6
2
影片教學

想盡辦法當好一個Junior Backend Developer系列 第 6

Golang - 如何當好一個junior backend developer - 來設計貓咪平台的schema吧

  • 分享至 

  • xImage
  •  

Yes

create role

CREATE ROLE meow_admin LOGIN PASSWORD 'admin_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE meow_user LOGIN PASSWORD 'user_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE meow_readonly LOGIN PASSWORD 'readonly_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE DATABASE meow_db;

ALTER DATABASE meow_db OWNER TO meow_admin;

psivilege

-- PUBLIC視為當前所有角色 --
REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public to meow_admin;
GRANT CREATE ON SCHEMA public to meow_admin;
GRANT USAGE ON SCHEMA public to meow_user;
GRANT USAGE ON SCHEMA public to meow_readonly;

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE users to meow_user;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE cats  to meow_user;

GRANT SELECT ON TABLE users to meow_readonly;
GRANT SELECT ON TABLE cats  to meow_readonly;

users table

CREATE TABLE users (
  id bigserial primary key,
  email varchar not null,
  password_digest varchar not null,
  name varchar not null,
  gender varchar not null,
  created_at timestamp with time zone not null default current_timestamp,
	updated_at timestamp with time zone not null default current_timestamp,
  deleted_at timestamp with time zone 
)

cats table

CREATE TABLE cats (
  id uuid primary key,
	user_id bigint not null,
	name vchar not null,
	gender vchar not null,

	created_at timestamp with time zone not null default current_timestamp,
	updated_at timestamp with time zone not null default current_timestamp,
  deleted_at timestamp with time zone 
)

constrains

ALTER TABLE cats ADD CONSTRAINT cats_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE;

Index

CREATE UNIQUE INDEX CONCURRENTLY users_fk_email ON users(email);
CREATE INDEX CONCURRENTLY users_index_created_at ON users(created_at);
CREATE INDEX CONCURRENTLY cats_index_created_at ON cats(created_at);

上一篇
Golang - 如何當好一個junior backend developer - 想想Primary Key設計這件事情
下一篇
Golang - 如何當好一個junior backend developer - 談談為何需要API Document
系列文
想盡辦法當好一個Junior Backend Developer13
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言